import datetime

from flask import Blueprint, request
from model.Plants import Plants, db as plantDb  # 这个变成新建的表的model
from model.userlike import userlike, db as userLikeDb
from utils.JsonUtil import to_json_all  # 转json
from utils.Result import Response  # 统一返回工具类
from common.database import runSql
import json
from sqlalchemy import desc
from utils.DateUtil import DateTimeConverter
import jieba.analyse
import pandas as pd
from itertools import chain

plants_blueprint = Blueprint('plants', __name__)
# 创建工具类实例
Result = Response()


# 获取所有植物列表(也是最新话题接口)
@plants_blueprint.route('/plants', methods=['GET'])
def get_plants():
    page = int(request.args.get("page"))
    size = 5  # 每页最大条数
    pageSize = page * size
    # 查询数据
    plants = Plants.query.order_by(desc(Plants.posted_date)).slice((page - 1) * size, pageSize).all()
    for plant in plants:
        plant.posted_date = plant.posted_date.strftime('%Y-%m-%d %H:%M:%S')
        plant.text = plant.text[:20] + "..."
    jsonData = to_json_all(plants)
    return Result.success(jsonData)


# 查询是否点赞
@plants_blueprint.route('/isLike', methods=['GET'])
def isLike():
    plant_id = request.args.get("id")
    user_id = request.args.get("userId")
    # 先查询用户是否点赞
    isLike = userlike.query.filter(userlike.user_id == user_id, userlike.plantId == plant_id).first()
    if isLike:
        return Result.success("用户已经点赞过了~")
    else:
        return Result.errorMsg("还没有点赞~")


# 用户点赞接口
@plants_blueprint.route('/like', methods=['GET'])
def like():
    plant_id = request.args.get("id")
    user_id = request.args.get("userId")
    # 先查询用户是否点赞
    # 先让plant表的对应数据点赞数+1
    plant = Plants.query.filter(Plants.plant_id == plant_id).first()
    isLike = userlike.query.filter(userlike.user_id == user_id, userlike.plantId == plant_id).first()
    if isLike:
        # 如果找到了对应的记录，减少点赞数
        plant.like_count -= 1
        userLikeDb.session().delete(isLike)
        userLikeDb.session().commit()
        return Result.success("用户取消了点赞~~")

    if plant:
        # 如果找到了对应的记录，增加点赞数
        plant.like_count += 1
        # 提交修改到数据库
        plantDb.session.commit()
        # 然后进行userLike表添加数据
        tb_like = userlike(user_id=user_id, plantId=plant_id)
        userLikeDb.session.add(tb_like)
        userLikeDb.session.commit()
        return Result.success("点赞成功")
    else:
        return Result.error("点赞失败")


# 获取植物列表总条数
@plants_blueprint.route('/getCount', methods=['GET'])
def getCount():
    # 所有数据查询
    totalCount = Plants.query.count()
    return Result.success(totalCount)


# 通过id查询文章详情
@plants_blueprint.route('/getPlantsById')
def getDescById():
    plant_id = request.args.get("id")
    # 查询数据
    plants = Plants.query.filter(Plants.plant_id == plant_id).all()
    for plant in plants:
        plant.posted_date = plant.posted_date.strftime('%Y-%m-%d %H:%M:%S')
    jsonData = to_json_all(plants)
    return Result.success(jsonData)


# 通过id获取文章名
@plants_blueprint.route('/getTitleById')
def getTitleById():
    plant_id = request.args.get("id")
    # 通过文章id获取title
    sql = "select title from plants where plant_id={}".format(plant_id)
    res = runSql(sql)
    if len(res) == 2:
        return Result.error()
    # 转字符编码

    return Result.success(json.loads(res)[0])


# 获取上一篇文章的id和标题
@plants_blueprint.route('/getPre')
def getPre():
    plant_id = request.args.get("id")
    # 查询id小于当前文章的id，limit限制1
    sql = "select plant_id,title from plants " \
          "where plant_id = (select plant_id from plants where plant_id < {} order by plant_id desc limit 1)".format(
        plant_id)
    res = runSql(sql)
    if len(res) == 2:
        return Result.error()
    # 转字符编码
    return Result.success(json.loads(res)[0])


# 获取下一篇文章的id和标题
@plants_blueprint.route('/getNext')
def getNext():
    plant_id = request.args.get("id")
    # 查询id大于当前文章的id，limit限制1
    sql = "select plant_id,title from plants " \
          "where plant_id = (select plant_id from plants where plant_id >{} order by plant_id asc  limit 1)".format(
        plant_id)
    res = runSql(sql)
    if len(res) == 2:
        return Result.error()
    # 转字符编码
    return Result.success(json.loads(res)[0])


# 通过阅读数获取分页数据
@plants_blueprint.route('/getAllByRead')
def getAllByRead():
    page = int(request.args.get("page"))
    size = 5  # 每页最大条数
    pageSize = page * size
    # 查询数据
    plants = Plants.query.order_by(desc(Plants.read_count)).slice((page - 1) * size, pageSize).all()
    for plant in plants:
        #  日期和字符串的最大长度限制
        plant.posted_date = plant.posted_date.strftime('%Y-%m-%d %H:%M:%S')
        plant.description = plant.description[:20] + "..."
    jsonData = to_json_all(plants)
    return Result.success(jsonData)


# 通过点赞数获取分页数据
@plants_blueprint.route('/getAllByLike')
def getAllByLike():
    page = int(request.args.get("page"))
    size = 5  # 每页最大条数
    pageSize = page * size
    # 查询数据
    plants = Plants.query.order_by(desc(Plants.like_count)).slice((page - 1) * size, pageSize).all()
    for plant in plants:
        #  日期和字符串的最大长度限制
        plant.posted_date = plant.posted_date.strftime('%Y-%m-%d %H:%M:%S')
        plant.description = plant.description[:20] + "..."
    jsonData = to_json_all(plants)
    return Result.success(jsonData)


# 主页，热门阅读接口
@plants_blueprint.route('/hot')
def hotReadTop5():
    # 通过阅读和点赞权重占比的热门排行
    sql = "SELECT   *,  (read_count * 0.7 + like_count * 0.3) AS weighted_score FROM plants  ORDER BY  weighted_score DESC  LIMIT 5"
    res = runSql(sql)
    if len(res) == 2:
        return Result.error()
    # 转字符编码

    dataList = json.loads(res)
    index = 0
    for data in dataList:
        # 格式化时间数据
        date = DateTimeConverter.convert_to_date_only(data["posted_date"])
        dataList[index]["posted_date"] = date
        index += 1

    return Result.success(dataList)


# 查询最近一天和一个月内新增的记录
@plants_blueprint.route('/getAddNumber')
def getAddNumber():
    # 通过union all合并的一天和一个月统计的sql
    sql = "SELECT count(*) as number FROM plants " \
          "WHERE DATE(posted_date) = CURDATE() " \
          "UNION ALL " \
          "SELECT count(*) as number FROM plants " \
          "WHERE date(posted_date) >= DATE_SUB(CURDATE(),INTERVAL 1 MONTH)"
    res = runSql(sql)
    if len(res) == 2:
        return Result.error()
    # 转字符编码
    dataList = json.loads(res)
    return Result.success(dataList)


# 热门话题功能
@plants_blueprint.route('/hotTopic')
def hotTopic():
    # 先查询前50个标题内容
    sql = "SELECT title FROM plants  ORDER BY posted_date desc limit 50"
    res = runSql(sql)
    dataList = json.loads(res)
    topicList = []
    # 分词
    for data in dataList:
        title = data["title"]
        topic = jieba.analyse.extract_tags(title, topK=20, withWeight=False, allowPOS=())
        topicList.append(topic)
    # 将嵌套的列表扁平化成一维列表
    flat_list = list(chain.from_iterable(topicList))
    # 转换为DataFrame
    df = pd.DataFrame({'Keyword': flat_list})
    # 找到重复的关键词
    duplicates = df[df.duplicated(keep=False)]
    # 统计每个关键词的出现次数并取出前六个
    top_keywords = duplicates['Keyword'].value_counts().nlargest(6)
    # 将top_keywords转换为JSON格式
    top_keywords_json = top_keywords.to_json(orient='columns')
    return Result.success(json.loads(top_keywords_json))


# 随机获取详情介绍
@plants_blueprint.route('/randDesc')
def randDesc():
    # 随机获取文章详情介绍
    sql = "select text from plants order by rand() limit 1"
    res = runSql(sql)
    dataList = json.loads(res)
    # 对文章进行的20个字的分隔，防止太长了
    dataList[0]["text"] = dataList[0]["text"][:40] + "...."
    return Result.success(dataList)


# 查询八张随机图片
@plants_blueprint.route('/randImg')
def randImg():
    # limit限制随机的8张图片地址
    sql = "SELECT image_path  FROM `plants` ORDER BY RAND() LIMIT 8"
    res = runSql(sql)
    dataList = json.loads(res)
    return Result.success(dataList)


# 列表接口
@plants_blueprint.route('/list')
def getList():
    # 传入搜索框内容
    search = request.args.get("search")
    page = int(request.args.get("page"))
    type_number = request.args.get("type")
    size = 6
    pageSize = page * size
    sql = None
    # 如果搜索内容不为空
    if  search != '' and search != "null":
        # 有搜索内容的时候进行搜索内容的分页（orm框架的语法）
        sql = Plants.query.filter(Plants.title.like("%" + search + "%")) \
            .order_by(desc(Plants.read_count)) \
            .all()
        jsonData = to_json_all(sql)
        return Result.success(jsonData)
    elif  type_number != '' and type_number != "null":
        sql = Plants.query.filter(Plants.type_number == type_number) \
            .order_by(desc(Plants.read_count)) \
            .slice((page - 1) * size, pageSize) \
            .all()
        jsonData = to_json_all(sql)
        return Result.success(jsonData)
    else:
        # 没有搜索内容，正常分页
        sql = Plants.query.order_by(desc(Plants.read_count)) \
            .order_by(desc(Plants.read_count)) \
            .slice((page - 1) * size, pageSize) \
            .all()
        jsonData = to_json_all(sql)
        return Result.success(jsonData)



# 列表接口的获取总量接口
@plants_blueprint.route('/listCount')
def getListCount():
    # 搜索接口配套获取总数量的接口
    search = request.args.get("search")
    type_number = request.args.get("type")
    sql = None
    # 如果搜索内容不为空
    if search != '' and search != "null":
        print("进行搜索" + search)
        sql = Plants.query.filter(Plants.title.like("%" + search + "%")) \
            .order_by(desc(Plants.read_count)) \
            .all()
        return Result.success(len(sql))
    elif type_number != '' and type_number != "null":
        sql = Plants.query.filter(Plants.type_number == type_number) \
            .order_by(desc(Plants.read_count)) \
            .all()
        return Result.success(len(sql))
    else:
        # 没有搜索内容，正常分页
        sql = Plants.query.order_by(desc(Plants.read_count)) \
            .order_by(desc(Plants.read_count)) \
            .all()
        return Result.success(len(sql))





# 添加文章
@plants_blueprint.route('/add', methods=['POST'])
def addPlant():
    # 获取 POST 请求的 JSON 数据
    data = request.get_json()
    # 获取具体字段
    title = data['title']
    desc = data['desc']
    imagePath = data['imagePath']
    text = data["text"]
    id = data["id"]
    typeNumber = data["typeNumber"]
    if len(imagePath) == 0:
        imagePath = "https://taritari-1306000991.cos.ap-chengdu.myqcloud.com/img%2F13.jpg"
    plants = Plants(title=title
                    , description=desc
                    , image_path=imagePath
                    , author_id=id
                    , posted_date=datetime.datetime.now()
                    , read_count=0
                    , like_count=0
                    , text=text
                    , type_number=typeNumber
                    )
    plantDb.session.add(plants)
    plantDb.session.commit()
    return Result.success("发布成功")


# 增加阅读量
@plants_blueprint.route('/addRead')
def addRead():
    id = request.args.get("id")
    # 先让plant表的对应数据点赞数+1
    plant = Plants.query.filter(Plants.plant_id == id).first()
    if plant:
        # 如果找到了对应的记录，增加点赞数
        plant.read_count += 1
        # 提交修改到数据库
        plantDb.session.commit()
    return Result.success("成功")


# 刪除文章
@plants_blueprint.route('/delete')
def deletePlant():
    # 通過id刪除plant表
    id = request.args.get("id")
    plantDb.session.query(Plants).filter(Plants.plant_id == id).delete()
    plantDb.session.commit()
    plantDb.session.close()
    # 通過id刪除userlike表
    userLikeDb.session.query(userlike).filter(userlike.plantId == id).delete()
    userLikeDb.session.commit()
    userLikeDb.session.close()
    return Result.success("刪除成功")


# 修改文章
@plants_blueprint.route('/update', methods=['POST'])
def updatePlants():
    # 获取 POST 请求的 JSON 数据
    data = request.get_json()
    # 获取具体字段
    title = data['title']
    imgPath = data['imgPath']
    desc = data['desc']
    id = data['id']
    text = data['text']
    # 先通过ID查询用户
    plant = Plants.query.filter(Plants.plant_id == id).first()
    if plant:
        plant.title = title
        plant.image_path = imgPath
        plant.description = desc
        plant.text = text
    # 提交修改到数据库
    plantDb.session.commit()
    return Result.success("修改成功")


# 分类


